﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Agresso.Interface.Layer.SQL
{
    public class AcrBatchInputSQL
    {
        public static string InsertAcrBatchInputSQL()
        {
            return @"
            INSERT INTO acrbatchinput
           ([account]
           ,[account2]
           ,[address]
           ,[allocation_key]
           ,[amount]
           ,[apar_id]
           ,[apar_name]
           ,[apar_type]
           ,[arrival_date]
           ,[arrive_id]
           ,[ba_country_code]
           ,[bank_acc_type]
           ,[bank_account]
           ,[base_amount]
           ,[base_curr]
           ,[batch_id]
           ,[clearing_code]
           ,[client]
           ,[client_ref]
           ,[collection]
           ,[commitment]
           ,[complaint]
           ,[contract_id]
           ,[country_code]
           ,[cur_amount]
           ,[curr_doc]
           ,[curr_licence]
           ,[currency]
           ,[dc_flag]
           ,[description]
           ,[dim_1]
           ,[dim_2]
           ,[dim_3]
           ,[dim_4]
           ,[dim_5]
           ,[dim_6]
           ,[dim_7]
           ,[disc_date]
           ,[discount]
           ,[due_date]
           ,[error_flag]
           ,[exch_rate]
           ,[exch_rate2]
           ,[exch_rate3]
           ,[ext_inv_ref]
           ,[ext_ref]
           ,[factor_short]
           ,[foreign_bank]
           ,[interface]
           ,[intrule_id]
           ,[kid]
           ,[number_1]
           ,[order_id]
           ,[pay_currency]
           ,[pay_flag]
           ,[pay_method]
           ,[pay_plan_id]
           ,[pay_temp_id]
           ,[pay_transfer]
           ,[period]
           ,[period_no]
           ,[place]
           ,[province]
           ,[rem_level]
           ,[remitt_id]
           ,[responsible]
           ,[sequence_no]
           ,[sequence_ref]
           ,[status]
           ,[swift]
           ,[tax_code]
           ,[tax_id]
           ,[tax_system]
           ,[trans_date]
           ,[trans_type]
           ,[value_1]
           ,[value_2]
           ,[value_3]
           ,[vat_reg_no]
           ,[voucher_date]
           ,[voucher_no]
           ,[voucher_ref]
           ,[voucher_type]
           ,[zip_code]
           ,[compl_delay]
           ,[orig_reference]
           ,[ext_arch_ref])
     VALUES
           (
            @account
            ,@account2
            ,@address
            ,@allocation_key
            ,@amount
            ,@apar_id
            ,@apar_name
            ,@apar_type
            ,@arrival_date
            ,@arrive_id
            ,@ba_country_code
            ,@bank_acc_type
            ,@bank_account
            ,@base_amount
            ,@base_curr
            ,@batch_id
            ,@clearing_code
            ,@client
            ,@client_ref
            ,@collection
            ,@commitment
            ,@complaint
            ,@contract_id
            ,@country_code
            ,@cur_amount
            ,@curr_doc
            ,@curr_licence
            ,@currency
            ,@dc_flag
            ,@description
            ,@dim_1
            ,@dim_2
            ,@dim_3
            ,@dim_4
            ,@dim_5
            ,@dim_6
            ,@dim_7
            ,@disc_date
            ,@discount
            ,@due_date
            ,@error_flag
            ,@exch_rate
            ,@exch_rate2
            ,@exch_rate3
            ,@ext_inv_ref
            ,@ext_ref
            ,@factor_short
            ,@foreign_bank
            ,@interface
            ,@intrule_id
            ,@kid
            ,@number_1
            ,@order_id
            ,@pay_currency
            ,@pay_flag
            ,@pay_method
            ,@pay_plan_id
            ,@pay_temp_id
            ,@pay_transfer
            ,@period
            ,@period_no
            ,@place
            ,@province
            ,@rem_level
            ,@remitt_id
            ,@responsible
            ,@sequence_no
            ,@sequence_ref
            ,@status
            ,@swift
            ,@tax_code
            ,@tax_id
            ,@tax_system
            ,@trans_date
            ,@trans_type
            ,@value_1
            ,@value_2
            ,@value_3
            ,@vat_reg_no
            ,@voucher_date
            ,@voucher_no
            ,@voucher_ref
            ,@voucher_type
            ,@zip_code
            ,@compl_delay
            ,@orig_reference
            ,@ext_arch_ref
            )";
        }
        public static string GetMaxAgrtIDAcrBatchInputSQL()
        {
            return @"select MAX(agrtid) from acrbatchinput";
        }
        public static string IsExistAcrBatchInputSQL()
        {
            return @"select count(*)  from acrbatchinput
                    where 
                    dim_4 = @dim_4
                    and voucher_date=@voucher_date
                    and description=@description
                    and cur_amount=@cur_amount
                    and curr_doc=@curr_doc
                    and ext_inv_ref = @ext_inv_ref
                    and due_date=@due_date
                        ";
        }
        public static string InsertAcrBatchInputInterfaceSQL()
        {
            return @"
            INSERT INTO i_acrbatchinput
           ([account]
           ,[account2]
           ,[address]
           ,[allocation_key]
           ,[amount]
           ,[apar_id]
           ,[apar_name]
           ,[apar_type]
           ,[arrival_date]
           ,[arrive_id]
           ,[ba_country_code]
           ,[bank_acc_type]
           ,[bank_account]
           ,[base_amount]
           ,[base_curr]
           ,[batch_id]
           ,[clearing_code]
           ,[client]
           ,[client_ref]
           ,[collection]
           ,[commitment]
           ,[complaint]
           ,[contract_id]
           ,[country_code]
           ,[cur_amount]
           ,[curr_doc]
           ,[curr_licence]
           ,[currency]
           ,[dc_flag]
           ,[description]
           ,[dim_1]
           ,[dim_2]
           ,[dim_3]
           ,[dim_4]
           ,[dim_5]
           ,[dim_6]
           ,[dim_7]
           ,[disc_date]
           ,[discount]
           ,[due_date]
           ,[error_flag]
           ,[exch_rate]
           ,[exch_rate2]
           ,[exch_rate3]
           ,[ext_inv_ref]
           ,[ext_ref]
           ,[factor_short]
           ,[foreign_bank]
           ,[interface]
           ,[intrule_id]
           ,[kid]
           ,[number_1]
           ,[order_id]
           ,[pay_currency]
           ,[pay_flag]
           ,[pay_method]
           ,[pay_plan_id]
           ,[pay_temp_id]
           ,[pay_transfer]
           ,[period]
           ,[period_no]
           ,[place]
           ,[province]
           ,[rem_level]
           ,[remitt_id]
           ,[responsible]
           ,[sequence_no]
           ,[sequence_ref]
           ,[status]
           ,[swift]
           ,[tax_code]
           ,[tax_id]
           ,[tax_system]
           ,[trans_date]
           ,[trans_type]
           ,[value_1]
           ,[value_2]
           ,[value_3]
           ,[vat_reg_no]
           ,[voucher_date]
           ,[voucher_no]
           ,[voucher_ref]
           ,[voucher_type]
           ,[zip_code]
           ,[compl_delay]
           ,[orig_reference]
           ,[ext_arch_ref]
            ,[agrtid])
     VALUES
           (
            @account
            ,@account2
            ,@address
            ,@allocation_key
            ,@amount
            ,@apar_id
            ,@apar_name
            ,@apar_type
            ,@arrival_date
            ,@arrive_id
            ,@ba_country_code
            ,@bank_acc_type
            ,@bank_account
            ,@base_amount
            ,@base_curr
            ,@batch_id
            ,@clearing_code
            ,@client
            ,@client_ref
            ,@collection
            ,@commitment
            ,@complaint
            ,@contract_id
            ,@country_code
            ,@cur_amount
            ,@curr_doc
            ,@curr_licence
            ,@currency
            ,@dc_flag
            ,@description
            ,@dim_1
            ,@dim_2
            ,@dim_3
            ,@dim_4
            ,@dim_5
            ,@dim_6
            ,@dim_7
            ,@disc_date
            ,@discount
            ,@due_date
            ,@error_flag
            ,@exch_rate
            ,@exch_rate2
            ,@exch_rate3
            ,@ext_inv_ref
            ,@ext_ref
            ,@factor_short
            ,@foreign_bank
            ,@interface
            ,@intrule_id
            ,@kid
            ,@number_1
            ,@order_id
            ,@pay_currency
            ,@pay_flag
            ,@pay_method
            ,@pay_plan_id
            ,@pay_temp_id
            ,@pay_transfer
            ,@period
            ,@period_no
            ,@place
            ,@province
            ,@rem_level
            ,@remitt_id
            ,@responsible
            ,@sequence_no
            ,@sequence_ref
            ,@status
            ,@swift
            ,@tax_code
            ,@tax_id
            ,@tax_system
            ,@trans_date
            ,@trans_type
            ,@value_1
            ,@value_2
            ,@value_3
            ,@vat_reg_no
            ,@voucher_date
            ,@voucher_no
            ,@voucher_ref
            ,@voucher_type
            ,@zip_code
            ,@compl_delay
            ,@orig_reference
            ,@ext_arch_ref
            ,@agrtid
            )";
        }
    }
}
